﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.LongER.Framework.Utils;
using Com.LongER.Business.ZBH001.ZBH001DSTableAdapters;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using Com.LongER.Framework.Utils.LogUtils;

namespace Com.LongER.Business.ZBH001
{
    public class CZBH001 : ABussinessBase
    {
        private Log log = Log.GetInstance();

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public ZBH001DS.M_TONGYONGDataTable GetLists()
        {
            String connectionString = ConnectionStringUtil.GetConnectionString();
            M_TONGYONGTableAdapter ta = new M_TONGYONGTableAdapter(connectionString);

            ZBH001DS.M_TONGYONGDataTable tb = ta.GetData();
            ta.Connection.Close();
            return tb;
        }

        ///// <summary> 
        ///// 分页  
        ///// summary> 
        ///// <typeparam name="T">typeparam> 
        ///// <param name="List">实现IEnumerableparam> 
        ///// <param name="FunWhere">delegate检索条件param> 
        ///// <param name="FunOrder">delegate排序param> 
        ///// <param name="PageSize">每页显示数param> 
        ///// <param name="PageIndex">当前页码param> 
        ///// <returns>returns> 
        //static IEnumerable<T> GetIenumberable<T>(IEnumerable<T> List, Func<T,
        //bool> FunWhere, Func<T, string> FunOrder, int PageSize, int PageIndex)
        //{
        //    var rance = List.Where(FunWhere).OrderByDescending(FunOrder).
        //    Select(t => t).Skip((PageIndex - 1) * PageSize).Take(PageSize);
        //    return rance;
        //} 

        //public void test()
        //{
        //    ZBH001DCDataContext dc = new ZBH001DCDataContext();
        //    var query1 = (from r in dc.T_ORDER
        //                  select r);
        //    var query2 = (from r in dc.M_TONGYONG
        //                  where r.CODE_CD == "SXX"
        //                  where r.DATA1 =="xxx"
        //                  select r);
        //    var query3 = (from r in dc.M_TONGYONG1
        //                  where r.CODE_CD == "TTT"
        //                  select r);
        //    var q = (from q1 in query1
        //             join q2 in query2 on q1.PINMING_CD equals q2.KEY1
        //             join q3 in query3 on q1.AGENT equals q3.KEY1
        //             select new
        //             {
        //                 q1.DANWEI,
        //                 q2.DATA1
        //             }).Skip(4 * 10).Take(10)
        //            ;

        //    foreach (var k in q)
        //    {


        //    }

        //    var query = from r in dc.T_ORDER
        //                .Where(a=>a.AGENT=="").Where(a=>a.BEIZHU=="")
        //                select r
        //                    ;
                        
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dicParam"></param>
        /// <returns></returns>
        public string GetOrderListJson(Dictionary<string, string> dicParam)
        {
            ZBH001DS.T_ORDERDataTable dt = this.GetOrderList(dicParam);
            string strJson = this.DataTable2Json(dt);
            return strJson;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="htParam"></param>
        /// <returns></returns>
        public ZBH001DS.T_ORDERDataTable GetOrderList(Dictionary<string, string> dicParam)
        {
            string strONO = dicParam["ONO"];
            string strZHUANGHUO_DATE = dicParam["ZHUANGHUO_DATE"];
            string strXIEHUO_DATE = dicParam["XIEHUO_DATE"];

            string strBETWEEN_START = ((int.Parse(dicParam["PAGE_NO"]) - 1) * 5 + 1).ToString();
            string strBETWEEN_END =  (int.Parse(dicParam["PAGE_NO"]) * 5).ToString();

            ////DB接続文字列取得
            var connectionString = ConnectionStringUtil.GetConnectionString();
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            string cnnstr = string.Empty;

            #region sqlContent
            // C#コード自動作成
            StringBuilder objSqlContent = new StringBuilder();
            objSqlContent.Append(" SELECT                                                                          ");
            objSqlContent.Append("     *                                                                           ");
            objSqlContent.Append(" FROM                                                                            ");
            objSqlContent.Append("     (                                                                           ");
            objSqlContent.Append("         SELECT                                                                  ");
            objSqlContent.Append("             T.*                                                                 ");
            objSqlContent.Append("             ,P.CODE1_HANZI PINM                                                 ");
            objSqlContent.Append("             ,C.CODE1_HANZI CLXT                                                 ");
            objSqlContent.Append("             ,J.CODE1_HANZI FKFS                                                 ");
            objSqlContent.Append("             ,D.CODE1_HANZI DDZT                                                 ");
            objSqlContent.Append("             ,rn = ROW_NUMBER                                                    ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("             OVER                                                                ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             ORDER BY                                                            ");
            objSqlContent.Append("                 T.ONO ASC                                                       ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("         FROM                                                                    ");
            objSqlContent.Append("             T_ORDER T                                                           ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG                                                          ");
            objSqlContent.Append("             P                                                                   ");
            objSqlContent.Append("             ON                                                                  ");
            objSqlContent.Append("                 T.PINMING_CD = P.KEY1                                           ");
            objSqlContent.Append("             AND P.CODE_CD = 'PINM'                                              ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG                                                          ");
            objSqlContent.Append("             C                                                                   ");
            objSqlContent.Append("             ON                                                                  ");
            objSqlContent.Append("                 T.CHELIANG_XINGTAI = C.KEY1                                     ");
            objSqlContent.Append("             AND C.CODE_CD = 'CLXT'                                              ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG                                                          ");
            objSqlContent.Append("             J                                                                   ");
            objSqlContent.Append("             ON                                                                  ");
            objSqlContent.Append("                 T.JIESUAN_TYPE = J.KEY1                                         ");
            objSqlContent.Append("             AND J.CODE_CD = 'FKFS'                                              ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG                                                          ");
            objSqlContent.Append("             D                                                                   ");
            objSqlContent.Append("             ON                                                                  ");
            objSqlContent.Append("                 T.ORDER_STATUS = D.KEY1                                         ");
            objSqlContent.Append("             AND D.CODE_CD = 'DDZT'                                              ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_KEHU                                                              ");
            objSqlContent.Append("             K                                                                   ");
            objSqlContent.Append("             ON                                                                  ");
            objSqlContent.Append("                 T.AGENT = K.KEHU_CD                                             ");
            objSqlContent.Append("         WHERE                                                                   ");
            objSqlContent.Append("                 1 = 1                                                           ");
            if (!String.IsNullOrWhiteSpace(strONO))
                objSqlContent.Append("             AND T.ONO = @ONO                                                    ");
            if (!String.IsNullOrWhiteSpace(strXIEHUO_DATE))    
                objSqlContent.Append("             AND T.ZHUANGHUO_DATE = @ZHUANGHUO_DATE                              ");
            if (!String.IsNullOrWhiteSpace(strXIEHUO_DATE))
                objSqlContent.Append("             AND T.XIEHUO_DATE = @XIEHUO_DATE                                    ");
            objSqlContent.Append("     )                                                                           ");
            objSqlContent.Append("     A                                                                           ");
            objSqlContent.Append(" WHERE                                                                           ");
            objSqlContent.Append("         A.rn BETWEEN @BETWEEN_START AND @BETWEEN_END;                           ");
            #endregion

            cnnstr = objSqlContent.ToString();

            log.Debug("CZBH001:" + cnnstr);

            SqlCommand cmd = new SqlCommand(cnnstr, con);
            cmd.CommandText = cnnstr;
            cmd.CommandType = System.Data.CommandType.Text;
            //
            if (!String.IsNullOrWhiteSpace(strONO))
                cmd.Parameters.Add(getSqlParameter("ONO", SqlDbType.Char, strONO));
            //
            if (!String.IsNullOrWhiteSpace(strXIEHUO_DATE))
                cmd.Parameters.Add(getSqlParameter("ZHUANGHUO_DATE", SqlDbType.Char, strZHUANGHUO_DATE));
            //
            if (!String.IsNullOrWhiteSpace(strXIEHUO_DATE))
                cmd.Parameters.Add(getSqlParameter("XIEHUO_DATE", SqlDbType.Char, strXIEHUO_DATE));
            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_START))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_START", SqlDbType.Int, int.Parse(strBETWEEN_START)));
            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_END))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_END", SqlDbType.Int, int.Parse(strBETWEEN_END)));

            SqlDataReader odr = cmd.ExecuteReader();

            ZBH001DS.T_ORDERDataTable tb = new ZBH001DS.T_ORDERDataTable();
            if (odr.HasRows)
            {
                // データ有り
                while (odr.Read())
                {
                    ZBH001DS.T_ORDERRow dr = tb.NewT_ORDERRow();
                    //row 1
                    dr.ONO = odr["ONO"].ToString();
                    dr.ZHUANGHUO_DATE = odr["ZHUANGHUO_DATE"].ToString();
                    dr.ZHUANGHUO_TIME = odr["ZHUANGHUO_TIME"].ToString();
                    dr.XIEHUO_DATE = odr["XIEHUO_DATE"].ToString();
                    dr.XIEHUO_TIME = odr["XIEHUO_TIME"].ToString();
                    dr.CHENGYUN_COSTS = decimal.Parse(odr["CHENGYUN_COSTS"] is DBNull ? "0" : odr["CHENGYUN_COSTS"].ToString());
                    dr.AGENTNO = odr["AGENTNO"].ToString();
                    dr.JIESUAN_TYPE_M = odr["FKFS"].ToString();
                    dr.ORDER_STATUS_M = odr["DDZT"].ToString();
                    dr.AGENT_LUOCHENG = odr["AGENT"].ToString() + odr["AGENT_LUOCHENG"].ToString();
                    dr.ZHUANGHUO_LUOCHENG = odr["ZHUANGHUO"].ToString() + odr["ZHUANGHUO_LUOCHENG"].ToString();
                    dr.XIEHUO_LUOCHENG = odr["XIEHUO"].ToString() + odr["XIEHUO_LUOCHENG"].ToString();

                    //row2
                    dr.PINMING_M = odr["PINM"].ToString();
                    dr.LENGTH = decimal.Parse(odr["LENGTH"] is DBNull ? "0" : odr["LENGTH"].ToString());
                    dr.WIDTH = decimal.Parse(odr["WIDTH"] is DBNull ? "0" : odr["WIDTH"].ToString());
                    dr.HIGHTH = decimal.Parse(odr["HIGHTH"] is DBNull ? "0" : odr["HIGHTH"].ToString());
                    dr.CHELIANG_XINGTAI_M = odr["CLXT"].ToString();
                    dr.WEIGHT = decimal.Parse(odr["WEIGHT"] is DBNull ? "0" : odr["WEIGHT"].ToString());
                    dr.COUNTS = decimal.Parse(odr["COUNTS"] is DBNull ? "0" : odr["COUNTS"].ToString());
                    dr.FAHUO_WEIGHT = decimal.Parse(odr["FAHUO_WEIGHT"] is DBNull ? "0" : odr["FAHUO_WEIGHT"].ToString());
                    dr.FAHUO_COUNTS = decimal.Parse(odr["FAHUO_COUNTS"] is DBNull ? "0" : odr["FAHUO_COUNTS"].ToString());
                    dr.WEIFA_WEIGHT = dr.WEIGHT - dr.FAHUO_WEIGHT;
                    dr.WEIFA_COUNTS = dr.COUNTS - dr.FAHUO_COUNTS;
                    dr.AGENT_LIANXI_REN = odr["AGENT_LIANXI_REN"].ToString()+"|"+ odr["AGENT_LIANXI_TEL"].ToString();
                    dr.ZHUANGHUO_LIANXI_REN = odr["ZHUANGHUO_LIANXI_REN"].ToString() + "|" + odr["ZHUANGHUO_LIANXI_TEL"].ToString();
                    dr.XIEHUO_LIANXI_REN = odr["XIEHUO_LIANXI_REN"].ToString() + "|" + odr["XIEHUO_LIANXI_TEL"].ToString();
                    

                                       

                    tb.AddT_ORDERRow(dr);
                }
            }

            con.Close();
            return tb;
        }


    }
}
